# Load necessary libraries
library(readxl)
library(ggplot2)
library(ggrepel)

# Set the path to your file
file_path <- "yourpath/100724_ARDL_con_uncon_coefficient_rearrange_48.xlsx"

# Read the data from Sheet2 of the Excel file
data <- read_excel(file_path, sheet = "Sheet2")

# Function to create plots for each state
create_plot_for_state <- function(state_name, data) {
  # Filter data for the given state
  state_data <- subset(data, State == state_name)
  
  # Convert Term1 to factor for correct ordering
  state_data$Term1 <- factor(state_data$Term1, levels = state_data$Term1)
  
  # Create base plot with ARDL_to_IDL and constrained_IDL lines
  plot <- ggplot(state_data, aes(x = Term1)) +
    geom_line(aes(y = ARDL_to_IDL, color = "ARDL to IDL", group = 1), size = 1.5, show.legend = FALSE) +
    geom_line(aes(y = constrained_IDL, color = "constrained IDL", group = 1), size = 1.5, show.legend = FALSE) +
    geom_hline(yintercept = 0, linetype = "dashed", color = "black", size = 1.5) +
    geom_text_repel(aes(x = Term1, y = ARDL_to_IDL, label = "ARDL to IDL"), data = state_data[1,], nudge_x = 0.3, nudge_y = 0, size = 6, color = "#1f78b4", fontface = "bold") +
    geom_text_repel(aes(x = Term1, y = constrained_IDL, label = "constrained IDL"), data = state_data[1,], nudge_x = -0.5, nudge_y = -0.02, size = 6, color = "#33a02c", fontface = "bold")
  
  # Conditionally add unconstrained_IDL if no missing values
  if (any(!is.na(state_data$unconstrained_IDL))) {
    plot <- plot + geom_line(aes(y = unconstrained_IDL, color = "unconstrained IDL", group = 1), size = 1.5, show.legend = FALSE) +
      geom_text_repel(aes(x = Term1, y = unconstrained_IDL, label = "unconstrained IDL"), data = state_data[1,], nudge_x = 0.5, nudge_y = 0.04, size = 6, color = "#ff7f00", fontface = "bold")
  }
  
  # Labels and theme setup
  plot <- plot + xlab("Terms of Lags") +
    ylab("IDL Coefficients (θi)") +
    scale_x_discrete(name = "Terms of Lags") +
    theme_classic(base_size = 20) +
    theme(
      axis.title.x = element_text(size = 20, face = "bold"),
      axis.title.y = element_text(size = 20, face = "bold"),
      axis.text.x = element_text(size = 18, face = "bold", color = "black"),
      axis.text.y = element_text(size = 18, face = "bold", color = "black"),
      legend.position = "none"
    ) +
    scale_color_manual(values = c("ARDL to IDL" = "#1f78b4", "unconstrained IDL" = "#ff7f00", "constrained IDL" = "#33a02c")) +
    coord_cartesian(clip = "off", expand = TRUE)
  
  # Save plot for the state
  ggsave(paste0("yourpath", state_name, "_IDL_Distribution07.png"), plot = plot, width = 12, height = 7, device = "png")
}

# Get the list of states
states <- unique(data$State)

# Loop through each state and generate a plot
for (state in states) {
  create_plot_for_state(state, data)
}
